package com.javasm.dao.MisMannageDao.impl;


import com.javasm.dao.MisMannageDao.ShowDao;
import com.javasm.entity.MisMannageEntity.ProdInfo;
import com.javasm.entity.MisMannageEntity.TbShow;
import com.javasm.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class ShowDaoImpl implements ShowDao {


    @Override
    public TbShow getrec(Integer id) {

        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT  relevancy_ids " +
                "FROM tb_show WHERE  show_prod_id= " + id);
        TbShow tbShow = new TbShow();

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                String relevancy_ids = resultSet.getString("relevancy_ids");
                tbShow.setRelevancyIds(relevancy_ids);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return tbShow;
    }

    @Override
    public Integer editSubs(String s, Integer id) {

        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        Integer resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("UPDATE tb_show SET " +
                "relevancy_ids = '" + s + "' WHERE " +
                "show_prod_id = " + id);
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeUpdate();
            //System.out.println(resultSet);
            //System.out.println(s + "=====");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, null);
        }

        return resultSet;
    }

    @Override
    public List<Integer> queSubs(String ids) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Integer> subids = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT  sh.show_prod_id " +
                "FROM tb_show sh WHERE sh.sort ");
        if (ids.split(",").length > 1) {
            stringBuilder.append("in (" + ids + ")");
        } else {
            stringBuilder.append(" = " + ids);
        }


        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Integer anInt = resultSet.getInt("sh.show_prod_id");
                subids.add(anInt);
            }
            //System.out.println(subids);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return subids;
    }

    @Override
    public Integer editshow(TbShow tbShow) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        Integer resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("UPDATE tb_show SET " +
                "show_recommend = ? , " +
                "`starting` = ? , " +
                "subscribe_upwards = ? , " +
                "visible = ? , " +
                "reasons = ? , " +
                "sort = ? WHERE " +
                "show_prod_id = ?");

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            preparedStatement.setString(1, tbShow.getShowRecommend());
            preparedStatement.setBoolean(2, tbShow.getStarting());
            preparedStatement.setBoolean(3, tbShow.getSubscribeUpwards());
            preparedStatement.setBoolean(4, tbShow.getVisible());
            preparedStatement.setString(5, tbShow.getReasons());
            preparedStatement.setInt(6, tbShow.getSort());
            preparedStatement.setInt(7, tbShow.getShowProdId());
            resultSet = preparedStatement.executeUpdate();
            //System.out.println(resultSet+"rrrrrrffgg");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, null);
        }

        return resultSet;
    }

    @Override
    public Integer addshow(TbShow tbShow) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        Integer resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("INSERT into tb_show" +
                "(show_prod_id,show_recommend,`starting`,subscribe_upwards,visible,reasons,sort) VALUES(" +
                tbShow.getShowProdId() + ",'" + tbShow.getShowRecommend() + "'," + tbShow.getStarting() + "," +
                tbShow.getSubscribeUpwards() + "," + tbShow.getVisible() + ",'" + tbShow.getReasons() + "'," + tbShow.getSort() + ")");
//        System.out.println(tbShow.getShowProdId());
//        System.out.println(tbShow.getShowRecommend());
//        System.out.println(tbShow.getStarting());
//        System.out.println(tbShow.getSubscribeUpwards());
//        System.out.println(tbShow.getVisible());
//        System.out.println(tbShow.getReasons());
//        System.out.println(tbShow.getSort());

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeUpdate();
            //System.out.println(resultSet + "rrrrrrffgg");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, null);
        }
        return resultSet;
    }

    @Override
    public List<TbShow> getProdName(String relevancyIds) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<TbShow> stringList = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();


        stringBuilder.append("SELECT  pi.prod_name ,pi.prod_id from tb_show sh ,tb_prod_info pi " +
                "WHERE pi.prod_id = sh.show_prod_id and pi.prod_id ");
        stringBuilder.append("in (" + relevancyIds + ")");

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                TbShow tbShow = new TbShow();
                String name = resultSet.getString("pi.prod_name");
                int id = resultSet.getInt("pi.prod_id");
                tbShow.setShowProdId(id);
                tbShow.setShowProdName(name);
                stringList.add(tbShow);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }


        return stringList;
    }

    @Override
    public List<ProdInfo> getProdName() {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<ProdInfo> stringList = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();


        stringBuilder.append("SELECT  pi.prod_name ,pi.prod_id from tb_prod_info pi " +
                "WHERE ( SELECT count(1) num FROM tb_show sh WHERE pi.prod_id = sh.show_prod_id ) = 0 ");

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                ProdInfo prodInfo = new ProdInfo();
                String name = resultSet.getString("pi.prod_name");
                int id = resultSet.getInt("pi.prod_id");
                prodInfo.setProdId(id);
                prodInfo.setProdName(name);
                stringList.add(prodInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }


        return stringList;
    }


    @Override
    public Integer getTotalnum(String prodName) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        Integer totalnum = null;
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("SELECT count(1) totalnum from tb_show sh , tb_prod_info pi WHERE pi.prod_id = sh.show_prod_id ");
        if (prodName != null) {
            stringBuilder.append("AND pi.prod_name LIKE '%" + prodName + "%' ");
        }
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                totalnum = resultSet.getInt("totalnum");
            }
            // System.out.println(totalnum + "开始");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }


        return totalnum;
    }

    @Override
    public List<TbShow> getShow() {

        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<TbShow> tbShowList = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("SELECT " +
                "pi.prod_name,sh.sort,pi.prod_id from tb_show sh ,tb_prod_info pi " +
                "WHERE pi.prod_id = sh.show_prod_id ");
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

                TbShow tbShow = new TbShow();
//                "pi.prod_id , " +
//                "pi.prod_name , " +
//                "sh.show_recommend , " +
//                "sh.sort , " +
//                "sh.`starting`," +
//                "sh.visible," +
//                "sh.subscribe_upwards
                tbShow.setShowProdName(resultSet.getString("pi.prod_name"));
                tbShow.setSort(resultSet.getInt("sh.sort"));
                tbShow.setShowProdId(resultSet.getInt("pi.prod_id"));

                tbShowList.add(tbShow);
                //  System.out.println(tbShow);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return tbShowList;
    }

    @Override
    public List<TbShow> getShowById(Integer page, Integer pageSize, String prodName) {

        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<TbShow> tbShowList = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("SELECT " +
                "pi.prod_id , " +
                "pi.prod_name , " +
                "sh.show_recommend , " +
                "sh.sort , " +
                "sh.starting," +
                "sh.visible," +
                "sh.subscribe_upwards from tb_show sh ,tb_prod_info pi " +
                "WHERE pi.prod_id = sh.show_prod_id " +
                "AND pi.prod_id in " +
                "(SELECT pi.prod_id from tb_prod_series ps ,tb_prod_info pi WHERE ps.series_id = pi.series_id ");
        if (prodName != null) {
            stringBuilder.append("AND ps.series_CHname LIKE '%" + prodName + "%'");
        }
        stringBuilder.append(")");
        stringBuilder.append(" LIMIT " + (page - 1) * pageSize + "," + pageSize);
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                TbShow tbShow = new TbShow();

//                "pi.prod_id , " +
//                "pi.prod_name , " +
//                "sh.show_recommend , " +
//                "sh.sort , " +
//                "sh.`starting`," +
//                "sh.visible," +
//                "sh.subscribe_upwards

                tbShow.setShowProdId(resultSet.getInt("pi.prod_id"));
                tbShow.setShowProdName(resultSet.getString("pi.prod_name"));
                tbShow.setShowRecommend(resultSet.getString("sh.show_recommend"));
                tbShow.setSort(resultSet.getInt("sh.sort"));
                tbShow.setStarting(resultSet.getBoolean("sh.starting"));
                tbShow.setVisible(resultSet.getBoolean("sh.visible"));
                tbShow.setSubscribeUpwards(resultSet.getBoolean("sh.subscribe_upwards"));
                tbShowList.add(tbShow);

                // System.out.println(tbShow);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }


        return tbShowList;
    }
}
